package com.xlh.util;

import cn.hutool.core.util.ReflectUtil;
import com.xlh.annotation.ExcelColumnAnnotation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 功能描述: excel解析工具
 */
public class ExcelParseUtils {

    private ExcelParseUtils() {
    }

    /**
     * 读取excel/csv 返回映射的实体类list
     * <p>
     * //     * @param fieldNameList 实体类的字段list，顺序和excel中列的顺序相同
     *
     * @param file  文件
     * @param clazz 实体类
     * @return
     */
    public static <T> List<T> readAll(MultipartFile file, int startLine, int sheetIndex, Class<T> clazz) throws Exception {
        if (file == null) {
            throw new IllegalArgumentException("请上传文件");
        }
        String fileName = file.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf("."));
        List<T> objects = new ArrayList<>();
        Workbook wb;
        switch (suffix) {
            case ".xls": // 2003 读取Excel
                wb = new HSSFWorkbook(file.getInputStream());
                objects = readXlsOrXlsxFile(wb, startLine, sheetIndex - 1, clazz);
                break;
            case ".xlsx": // 2007
                wb = new XSSFWorkbook(file.getInputStream());
                objects = readXlsOrXlsxFile(wb, startLine, sheetIndex - 1, clazz);
                break;
            case ".csv": //支持csv文件
//                objects = readCsvFile(fieldNameList, file, clazz);
                break;
            default:
                throw new IllegalArgumentException("不支持的文件类型");
        }
        return objects;
    }


    private static <T> List<T> readCsvFile(List<String> fieldNameList, MultipartFile file1, Class<T> clazz) throws Exception {
        List<T> objects = new ArrayList<T>();
        BufferedReader br = new BufferedReader(new InputStreamReader(file1.getInputStream(), StandardCharsets.UTF_8));//这里如果csv文件编码格式是utf-8,改成utf-8即可
        int rowNum = 0;
        String line = null;
        Map<String, Field> fieldMap = getField(clazz);//保存实体类的字段信息
        while ((line = br.readLine()) != null) {
            rowNum++;
            if (rowNum == 1) {
                continue;
            }

            T entity = null;
            String[] rows = line.split(";");
            for (int i = 0; i < rows.length; i++) {
                String c = rows[i];
                entity = (entity == null ? clazz.newInstance() : entity);
                String fieldName = fieldNameList.get(i);
                Field field = fieldMap.get(fieldName);
                setFields(entity, field, fieldName, c);
            }
            if (entity != null) {
                objects.add(entity);
            }
        }

        return objects;
    }

    private static <T> List<T> readXlsOrXlsxFile(Workbook wb, int startLine, int sheetIndex, Class<T> clazz) throws Exception {
        List<T> objects = new ArrayList<T>();
        // 获取sheet数目
        Sheet sheet = wb.getSheetAt(sheetIndex);
        int lastRowNum = sheet.getLastRowNum();
        // 循环读取,（去掉第一行）
        Map<String, Field> fieldMap = getField(clazz);
        List<String> fieldNameList = getFieldName(clazz);
        for (int i = startLine - 1; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            T entity = null;
            if (row != null) {

                if (row.getLastCellNum() != fieldMap.size()) {
                    throw new IllegalArgumentException("Excel模板类型不正确,请上传正确的Excel文件");
                }

                // 获取每一列的值
                for (int j = 0; j < row.getLastCellNum() && j < fieldNameList.size(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    String c = getCellValue(cell);
                    if (c == null) {
                        continue;
                    }
                    entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
                    String fieldName = fieldNameList.get(j);
                    Field field = fieldMap.get(fieldName);

                    setFields(entity, field, fieldName, c);
                }
                if (entity != null) {
                    objects.add(entity);
                }
            }
        }
        return objects;
    }

    private static <T> List<String> getFieldName(Class<T> clazz) {
        List<String> list = new ArrayList<>();
        Field[] fields = sortFields(ReflectUtil.getFields(clazz));
        for (Field field : fields) {
            list.add(field.getName());
        }
        return list;
    }

    private static <T> Map<String, Field> getField(Class<T> clazz) {
        Map<String, Field> map = new HashMap<>();
        Field[] fields = ReflectUtil.getFields(clazz);
        for (Field field : fields) {
            map.put(field.getName(), field);
        }
        return map;
    }

    private static Field[] sortFields(Field[] fields) {
        Field[] result = new Field[fields.length];
        for (Field field : fields) {
            result[field.getAnnotation(ExcelColumnAnnotation.class).collom() - 1] = field;
        }
        return result;
    }

    /***
     * 读取单元格的值
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    result = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    DecimalFormat df = new DecimalFormat("#");//转换成整型
                    return df.format(cell.getNumericCellValue());
                case BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    result = cell.getCellFormula();
                    break;
                case ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case BLANK:
                    break;
                default:
                    break;
            }
        }
        return result.toString();
    }

    private static <T> void setFields(T entity, Field field, String fieldName, String c) throws Exception {
        // 取得类型,并根据对象类型设置值.
        Class<?> fieldType = field.getType();
        try {
            if (String.class == fieldType) {
                entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, c);
            } else if ((Integer.TYPE == fieldType)
                    || (Integer.class == fieldType)) {
                if (c.contains(".")) {
                    entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Integer.parseInt(c.substring(0, c.indexOf("."))));
                } else {
                    entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Integer.parseInt(c));
                }
            } else if ((Long.TYPE == fieldType)
                    || (Long.class == fieldType)) {
                if (c.contains(".")) {
                    entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Long.parseLong(c.substring(0, c.indexOf("."))));
                } else {
                    entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Long.parseLong(c));
                }
            } else if ((Float.TYPE == fieldType)
                    || (Float.class == fieldType)) {
                entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Float.valueOf(c));
            } else if ((Short.TYPE == fieldType)
                    || (Short.class == fieldType)) {
                entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Short.valueOf(c));
                if (c.contains(".")) {
                    entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Short.valueOf(c.substring(0, c.indexOf("."))));
                } else {
                    entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Short.valueOf(c));
                }
            } else if ((Double.TYPE == fieldType)
                    || (Double.class == fieldType)) {
                entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, Double.valueOf(c));
            } else if ((Date.class == fieldType)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date date = sdf.parse(c);
                entity.getClass().getMethod("set" + StringUtils.capitalize(fieldName), field.getType()).invoke(entity, date);
            }
        } catch (IllegalAccessException e) {
            throw new IllegalAccessException("参数异常!");
        } catch (NoSuchMethodException e) {
            throw new NoSuchMethodException("未找到 " + "set" + StringUtils.capitalize(fieldName) + " 方法!");
        } catch (Exception e) {
            throw new Exception(field.getName() + "数据错误!");
        }
    }

}